package org.example;

import org.gridgain.internal.h2.tools.DeleteDbFiles;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MyStackData_1 {

    public void insert_data(String table_name, List<List<Object>> lst) throws SQLException, ClassNotFoundException {
        Class.forName("org.gridgain.internal.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:gg-h2:/Users/chenfei/temp/db/" + table_name);

        for (List<Object> r : lst) {
            PreparedStatement statement = conn.prepareStatement("insert into " + table_name + " (st_timestamp, volume, open, high, low, close, chg, percent, turnoverrate, amount, volume_post, amount_post, pe, pb, ps, pcf, market_capital, balance, hold_volume_cn，hold_ratio_cn，net_volume_cn，hold_volume_hk，hold_ratio_hk，net_volume_hk，symbol) " +
                    "values (?, ?, ?, ?, ?, " +
                    "?, ?, ?, ?, ?," +
                    "?, ?, ?, ?, ?," +
                    "?, ?, ?, ?, ?," +
                    "?, ?, ?, ?, ?)");
            statement.setDate(1, new Date((int)r.get(0)));
            statement.setDouble(2, r.get(1) != null? (Double)r.get(1): null);
            statement.setDouble(3, r.get(2) != null? (Double)r.get(2): null);
            statement.setDouble(4, r.get(3) != null? (Double)r.get(3): null);
            statement.setDouble(5, r.get(4) != null? (Double)r.get(4): null);
            statement.setDouble(6, r.get(5) != null? (Double)r.get(5): null);
            statement.setDouble(7, r.get(6) != null? (Double)r.get(6): null);
            statement.setDouble(8, r.get(7) != null? (Double)r.get(7): null);
            statement.setDouble(9, r.get(8) != null? (Double)r.get(8): null);
            statement.setDouble(10, r.get(9) != null? (Double)r.get(9): null);
            statement.setDouble(11, r.get(10) != null? (Double)r.get(10): null);

            statement.setDouble(12, r.get(11) != null? (Double)r.get(11): null);
            statement.setDouble(13, r.get(12) != null? (Double)r.get(12): null);
            statement.setDouble(14, r.get(13) != null? (Double)r.get(13): null);
            statement.setDouble(15, r.get(14) != null? (Double)r.get(14): null);
            statement.setDouble(16, r.get(15) != null? (Double)r.get(15): null);
            statement.setDouble(17, r.get(16) != null? (Double)r.get(16): null);
            statement.setDouble(18, r.get(17) != null? (Double)r.get(17): null);
            statement.setDouble(19, r.get(18) != null? (Double)r.get(18): null);
            statement.setDouble(20, r.get(19) != null? (Double)r.get(19): null);
            statement.setDouble(21, r.get(20) != null? (Double)r.get(20): null);
            statement.setDouble(22, r.get(21) != null? (Double)r.get(21): null);
            statement.setDouble(23, r.get(22) != null? (Double)r.get(22): null);
            statement.setDouble(24, r.get(23) != null? (Double)r.get(23): null);
            statement.setString(25, r.get(24) != null? (String) r.get(24): null);

            statement.executeUpdate();
        }
        conn.close();
    }

    public void create_table(String table_name) throws ClassNotFoundException, SQLException {
        DeleteDbFiles.execute("/Users/chenfei/temp/db", table_name, true);

        Class.forName("org.gridgain.internal.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:gg-h2:/Users/chenfei/temp/db/" + table_name);
        Statement stat = conn.createStatement();

        // this line would initialize the database
        // from the SQL script file 'init.sql'
        // stat.execute("runscript from 'init.sql'");

        stat.execute("create table IF NOT EXISTS "+ table_name +"(" +
                "    id long AUTO_INCREMENT," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10)," +
                "    primary key(id))");

        stat.close();
        conn.close();
    }

    public void create_index(String tableName, String... indexs) throws ClassNotFoundException, SQLException {

        StringBuilder sb = new StringBuilder();
        StringBuilder sb1 = new StringBuilder();
        if (indexs.length > 0)
        {

            for (int i = 0; i < indexs.length; i++)
            {
                if (i == indexs.length - 1)
                {
                    sb1.append(indexs[i]);
                }
                else
                {
                    sb1.append(indexs[i] + ",");
                }
                sb.append(indexs[i] + "_");
            }
        }
        sb.append("idx");

        String table_name = tableName + "_" + sb.toString();
        String sql = "CREATE INDEX IF NOT EXISTS "+ table_name +" ON "+ tableName +" ("+ sb1.toString() +")";
        //DeleteDbFiles.execute("/Users/chenfei/temp/db", tableName, true);

        Class.forName("org.gridgain.internal.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:gg-h2:/Users/chenfei/temp/db/" + tableName);
        Statement stat = conn.createStatement();

        // this line would initialize the database
        // from the SQL script file 'init.sql'
        // stat.execute("runscript from 'init.sql'");

        stat.execute(sql);

        stat.close();
        conn.close();
    }

    @Test
    public void create_table() throws SQLException, ClassNotFoundException {
        List<String> lst = new ArrayList<>();
        lst.add("stock_date");
        lst.add("stock_week");
        lst.add("stock_month");
        lst.add("stock_quarter");
        lst.add("stock_year");
        lst.add("stock_120m");
        lst.add("stock_60m");
        lst.add("stock_30m");
        lst.add("stock_15m");
        lst.add("stock_5m");
        lst.add("stock_1m");

        for (String tn : lst)
        {
            create_table(tn);
            create_index(tn, "symbol");
        }
    }
}






































